// tabelDatasets.js
const fs = require('fs');
const BaseDatasetWrapper = require('../../labels/BaseDatasetWrapper');

class DatasetsTable {
    constructor(app, db) {
        this.app = app;
        this.db = db;
    }

    init() {
        // 创建dataset list表结构
        const sqlDatasetsTable = `
        CREATE TABLE IF NOT EXISTS datasets (
            id TEXT PRIMARY KEY,
            dataset_name TEXT NOT NULL,
            dataset_supertype TEXT,
            dataset_type TEXT NOT NULL,
            dataset_dir TEXT NOT NULL,
            label_format TEXT,
            total_samples INTEGER,
            labeled_samples INTEGER,
            status TEXT DEFAULT 'idle',
            percent INTEGER DEFAULT 0,
            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            dir_available INTEGER DEFAULT 1
        );`;

        this.db.run(sqlDatasetsTable, (err) => {
            if (err) {
                console.error('Create "datasets" Table Failed, Error: ', err);
            }
        });
    }

    async hasDataset(dataset_id) {
        const selectStatement = `
            SELECT COUNT(*) AS count
            FROM datasets
            WHERE id = ?;
        `;

        return new Promise((resolve, reject) => {
            this.db.get(selectStatement, [dataset_id], (err, row) => {
                if (err) {
                    reject(err.message);
                } else {
                    resolve(row.count > 0);
                }
            });
        });
    }

    async isDirectoryUsed(datasetType, directory) {
        // console.log(`isDirectoryUsed datasetType = ${datasetType}, directory = ${directory}`);
        return new Promise((resolve, reject) => {
            const sqlCheckExists = `SELECT 1 FROM datasets WHERE dataset_type = ? AND dataset_dir = ?`;
            this.db.get(sqlCheckExists, [datasetType, directory], (err, row) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(row ? true : false);
                }
            });
        });
    }

    async insertDataset(datasetInfos) {
        const { id, datasetName, datasetSuperType, datasetType, datasetDir, labelFormat, totalSamples, labeledSamples } = datasetInfos;
        // console.log('labelFormat = ', labelFormat);
        return new Promise((resolve, reject) => {
            const sqlInsert = `
                INSERT INTO datasets (id, dataset_name, dataset_supertype, dataset_type, dataset_dir, label_format, total_samples, labeled_samples, create_time, update_time)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime(CURRENT_TIMESTAMP, '+8 hour') , datetime(CURRENT_TIMESTAMP, '+8 hour'));`;
            this.db.run(sqlInsert, [id, datasetName, datasetSuperType, datasetType, datasetDir, labelFormat, totalSamples, labeledSamples], function (err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(id);
                }
            });
        });
    }

    // 获取dataset的基础信息
    async getDatasetInfo(datasetId) {
        // console.log('datasetId = ', datasetId);
        return new Promise((resolve, reject) => {
            const sqlQuery = `SELECT * FROM datasets WHERE id = ?`;
            this.db.all(sqlQuery, [datasetId], (err, rows) => {
                if (err) {
                    reject(null);
                } else {
                    resolve(rows[0]);
                }
            });
        });
    }

    async getDatasetList(datasetSuperType, datasetType) {
        return new Promise((resolve, reject) => {
            let sqlQuery = `SELECT * FROM datasets`;
            const conditions = [];
            const params = [];

            if (datasetSuperType) {
                conditions.push(`dataset_supertype = ?`);
                params.push(datasetSuperType);
            }

            if (datasetType) {
                conditions.push(`dataset_type = ?`);
                params.push(datasetType);
            }

            if (conditions.length > 0) {
                sqlQuery += ` WHERE ` + conditions.join(' AND ');
            }

            this.db.all(sqlQuery, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // 删除数据集记录
    async deleteDataset(datasetId) {
        return new Promise((resolve, reject) => {
            const sqlDeleteDataset = `DELETE FROM datasets WHERE id = ?;`;
            this.db.run(sqlDeleteDataset, [datasetId], async (err) => {
                if (err) {
                    console.error('Error deleting dataset:', err.message);
                    reject({ code: -1, msg: err.message });
                }
                // 确认删除后则返回结果
                resolve({ code: 0, msg: 'Deleted OK' });
            });
        });
    }

    // 更新数据集记录
    async updateDataset(datasetInfo) {
        return new Promise(async (resolve, reject) => {
            let sqlUpdateDataset = 'UPDATE datasets SET';
            const params = [];

            if (datasetInfo.dataset_dir !== null && datasetInfo.dataset_dir !== undefined) {
                sqlUpdateDataset += ' dataset_dir = ?,';
                params.push(datasetInfo.dataset_dir);
            }

            if (datasetInfo.label_format !== null && datasetInfo.label_format !== undefined) {
                sqlUpdateDataset += ' label_format = ?,';
                params.push(datasetInfo.label_format);
            }

            if (datasetInfo.status !== null && datasetInfo.status !== undefined) {
                sqlUpdateDataset += ' status = ?,';
                params.push(datasetInfo.status);
            }

            if (datasetInfo.percent !== null && datasetInfo.percent !== undefined) {
                sqlUpdateDataset += ' percent = ?,';
                params.push(datasetInfo.percent);
            }

            // Remove the trailing comma and add the WHERE clause
            sqlUpdateDataset += " update_time = datetime(CURRENT_TIMESTAMP, '+8 hour') WHERE id = ?;";
            params.push(datasetInfo.id);

            // update datasets parse result
            if (datasetInfo.dataset_dir && datasetInfo.label_format) {
                const datasetTmpDir = path.join(this.userDataPath, 'datasets', datasetInfo.id.toString());
                try {
                    if (fs.existsSync(datasetTmpDir)) {
                        fs.rmSync(datasetTmpDir, { recursive: true });
                        console.log(`Datasets directory ${datasetTmpDir} deleted!`);
                    }
                } catch (err) {
                    console.error(`Error deleting datasets directory: ${err}`);
                    reject({ code: -2, msg: `Update ${datasetTmpDir} failed!` });
                }

                let result = await BaseDatasetWrapper.invokeParse(this.app, datasetInfo);
                if (result.code < 0) {
                    reject(result);
                }
            }

            // Execute the UPDATE SQL statement
            this.db.run(sqlUpdateDataset, params, (err) => {
                if (err) {
                    console.error('Error updating dataset:', err.message);
                    reject({ code: -1, msg: err.message });
                }

                // console.log(`Dataset with ID ${datasetInfo.id} updated successfully.`);
                resolve({ code: 0, msg: 'Updated OK' });
            });
        });
    }
}

module.exports = { DatasetsTable };
